Oracle 如何使用 SQLT 进行 SQL 调优

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 如何使用 SQLT 进行 SQL 调优，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！

SQLT：SQLTXPLAIN，也被称为 SQLT，是由 Oracle 服务器技术中心- ST CoE 提供的工具。SQL main methods 输入一条 SQL 语句并输出一组诊断文件。这些文件通常用于诊断执行不良的 SQL 语句。SQL 连接到数据库并收集执行计划、基于成本的Optimizer CBO 统计信息、模式对象元数据、性能统计信息、配置参数和影响所分析SQL的性能的类似元素。对于 tuning SQL, SQLT 需要一定程度的专业知识才能最大限度地利用它。对于大多数问题，我们建议您首先使用 SQL 运行状况检查(SQLHC：SQL Health Check)检查查询，如果无法解决问题，则转到 SQLT。

SQLT main methods 连接到数据库并收集执行计划、基于成本的优化器 CBO 统计信息、架构对象元数据、性能统计信息、配置参数以及影响正在分析的一个 SQL 的性能的其他元素。

在安装此工具期间，将创建 2 个用户 SQLTXADMIN 和 SQLTXPLAIN 以及一个角色 SQLT_USER_ROLE。sql 存储库由用户 SQLTXPLAIN 拥有。每次使用任何 main 方法时，sql 用户都需要提供 SQLTXPLAIN 密码。SQLTXPLAIN 用户被授予以下系统权限:
• CREATE SESSION
• CREATE TABLE

PL/SQL包和视图的 SQL 集由用户SQLTXADMIN拥有。该 SQLTXADMIN 用户被锁定并由随机密码标识。SQLTXADMIN 被授予以下系统特权:
• ADMINISTER SQL MANAGEMENT OBJECT
• ADMINISTER SQL TUNING SET
• ADVISOR
• ALTER SESSION
• ANALYZE ANY
• SELECT ANY DICTIONARY
• SELECT_CATALOG_ROLE

所有的 SQL 用户在使用任何main方法之前都必须被授予 SQLT_USER_ROLE。该 SQLT_USER_ROLE 角色被授予以下系统权限:
• ADVISOR
• SELECT_CATALOG_ROLE

注意：不建议使用 SYS 或其他 DBA 账户来运行主要方法，因为收集可能会失败。授予 SQLT_USER_ROLE 并运行主要方法的最佳用户是应用程序的所有者。

如果必须使用 SYS 或其他 DBA 账户，那么在 12c 中，由于 PL/SQL 的安全模型的更改，需要手动执行额外的授权。要解决此更改，需要在 SYS 或 DBA 账户上授予 SQLTXADMIN 用户 INHERIT PRIVILEGES 继承特权。

GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;




下载 SQLT

MOS 下载地址：Download SQLTXPLAIN (SQLT)  (Doc ID 215187.1)
sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip，也可添加我个人微信【JiekeXu_DBA】获取。

墨天轮下载地址：https://www.modb.pro/doc/86093

安装方法

unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip

以 SYS 用户执行 ./sqlt/install/sqcreate.sql 脚本，需要输入以下信息。

这里说一下 Oracle pack license 这里，SQLT 本身不需要 licence，输入 T 的话收集的结果会包含 sql tuning 结果，输入 D 的话会包含 awr 报告信息，输入 N 这里就不包含这两项信息。如果不想使用 SQLT 的话可以使用脚本 ./sqlt/install/sqdrop.sql 直接卸载。

安装过程示例

这里以单机 12.2.0.1 多租户环境为例，首先进入到容器 JIEKEXUPDB1 下，可创建 SQLT 用户 SQLTXPLAIN，也可以不用建，跑脚本时自动创建 SQLTXPLAIN 用户。

然后执行脚本 @sqcreate.sql 输入连接串，创建 SQLT 用户的密码以及默认表空间等信息。

输入 T 后稍等一会当看到如下信息说明安装成功。

SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.

然后可以把 SQLT 角色 SQLT_USER_ROLE 给予其他业务用户，例如 SCOTT 等其他业务用户。

涉及到的主要 SQL

SYS@JiekeXu> alter session set container=JIEKEXUPDB1;
SYS@JiekeXu> create user SQLTXPLAIN identified by SQLTXPLAIN; --当然也可以不用提前创建用户
SYS@JiekeXu> grant CREATE SESSION,CREATE TABLE TO  SQLTXPLAIN;
SYS@JiekeXu> @sqcreate.sql
  adding: 221113152749_01_sqcreate.log (deflated 87%)
Optional Connect Identifier (ie: @PROD): @JIEKEXUPDB1 <--- 输入 TNS 连接名

Password for user SQLTXPLAIN:      <--- 输入专用用户密码
Re-enter password:                 <--- 再次输入专用用户密码

Type YES or NO [Default NO]: YES     <--- 输入专用用户表空间和临时表空间名，大写
Default tablespace [USERS]: USERS
Temporary tablespace [TEMP]: TEMP
Main application user of SQLT: SQLTXPLAIN  <--- 输入专用用户名，大写

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

Oracle Pack license [T]: T               <--- 输入 license T 
SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.

SYS@JiekeXu> GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;
SYS@JiekeXu> grant SQLT_USER_ROLE to sys;
SYS@JiekeXu> grant SQLT_USER_ROLE to SCOTT;




主要的执行方法一般是 sqltxtract.sql 加 sqlid, 输入 SQLT 密码，然后便会生成 sqlt+日期+sqlid 结尾的 zip 包，解压后内容很多,我们一般情况下只需要关注 sqlt*lite.html 和 sqlt*main.html 结尾的文件就好，sqltlite.html 算是轻量级的 SQLT，sqltmain.html 是详细的信息。XTRACT Method、XECUTE Method 等其他方法可参考 SQLT 的安装介绍文档：sqlt_instructions.html。

SCOTT@jiekexupdb1> select count(*) from scott.t a,scott.test b where a.object_id=b.object_id /*JIekeXu*/; 

  COUNT(*)
----------
     72783
SCOTT@jiekexupdb1> select sql_id,sql_text from v$sql where sql_text like '%JIekeXu%' and sql_text not like '%like%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4mvsgjurg63fd
select count(*) from scott.t a,scott.test b where a.object_id=b.object_id /*JIekeXu*/

SCOTT@jiekexupdb1>@/home/oracle/tmp/SQLT/sqlt/run/sqltxtract.sql 4mvsgjurg63fd

--然后输入 SQLT 密码即可。

13:57:03 SYS@test> @/u01/soft/SQLT/sqlt/run/sqltxtract.sql 66jty4hfyt8mh SQLTXPLAIN     --也可以直接跟 sqltxplain 密码

再次说明不建议使用 SYS 或其他 DBA 账户来运行 main 方法，因为集合可能会失败。

授予和运行 main 方法的最佳用户是应用程序的所有者。

执行期间可查看这个视图监控执行过程：

SELECT * FROM SQLTXADMIN.sqlt$_log_v;

然后会生成一个以 sqlt+日期+sqlid_S.zip 的文件，sqlt_20221116_1428_60jj9axkt0v9u_S.zip  解压后有如下 20 个文件：

sqlt_s51483_10053_i1_c1_extract.trc  sqlt_s51483_cell_state.zip  sqlt_s51483_main.html               sqlt_s51483_sqldx.zip      sqlt_s51483_tcx.zip
sqlt_s51483_addmrpt_0007.zip         sqlt_s51483_driver.zip      sqlt_s51483_opatch.zip              sqlt_s51483_tcb.zip        sqlt_s51483_tc.zip
sqlt_s51483_ashrpt_0007.zip          sqlt_s51483_lite.html       sqlt_s51483_readme.html             sqlt_s51483_tc_script.sql  sqlt_s51483_trc.zip
sqlt_s51483_10053_explain.trc  sqlt_s51483_awrrpt_0007.zip          sqlt_s51483_log.zip         sqlt_s51483_sql_detail_active.html  sqlt_s51483_tc_sql.sql

sqlt*lite.html 内容

这个文件算是轻量级的 sqlt 了，里面包含六大块内容，主要涉及到表、索引、索引列、执行计划这些信息。

sqlt*main.html 内容

215187.1 SQLT XTRACT 19.1.200226  Report: sqlt_s51484_main.html

Main 文件内容更加丰富，主要包含以下八类信息。带有下划线的内容点击均可进入到相应的模块，大家可自行点击查看，尤其 Golbal 相关的信息，更为重要，建议大家详细查看。

如下示例，列出了数据库中非默认的优化器参数及参数值。

SQLHC

这里顺便说一句比 SQLT 更简洁的 sqlhc 工具，这个工具收集的信息也很全面，值得大家尝试，研读。

上传 sqlhc 文件，公众号后台回复【sqlhc】获取，输入 T 和 sqlid 即可生成。sqlhc 是 SQL health check的简称，能够收集sql相关的表、索引、统计信息、优化器参数、SQL执行情况、等待事件等信息，可以帮你检查SQL存在的问题并优化 SQL。

执行方法：sqlplus / as sysdba
SQL> @/home/oracle/tmp/sqlhc.sql T 9a4tv1dduu9u4
或者
SQL> @/home/oracle/tmp/sqlhc.sql
Parameter 1:
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
Enter value for 1: T
PL/SQL procedure successfully completed.
Parameter 2:
SQL_ID of the SQL to be analyzed (required)
Enter value for 2: 9a4tv1dduu9u4       <----输入 sql_id 等待 5 分钟左右




时间有可能更长或者更短(根据 AWR 保存周期、字典表大小不同相差较大，一般系统应该在 5 分钟以内能够完成)，对数据库没有影响。执行过程有 log，也有屏幕输出。执行过程会 insert 数据到 plan_table 表，执行结束时会 rollback。

结束后生成的文件名以 sqlhc 开头，依次是日期、时间、sql_id。类似这样：sqlhc_20211125_1810_9a4tv1dduu9u4.zip

其中 4 个 html 文件和 log.zip 是通常存在的。

10053 trace 文件的生成需要 11.2 版本以上，sql_id 仍在 library cache 内的情况下。

如果 sql_monitor.zip 也包含在 sqlhc 压缩包内，说明 SQL 执行时间超过了 5s，或者是并行的 SQL，而且收集 sqlhc 时仍保留在 sql monitor 的内存里。sql monitor 对分析 sql 执行计划有很大帮助，如果遇到问题收集 sqlhc 信息及时，就非常有可能收集到 sql monitor 文件。如果一个 sql 执行完后超过半小时没有收集 sqlhc，sql monitor 信息就就非常有可能被刷出内存。

主要分析的的 3 个 html 文件是：

*_health_check.html
*_diagnostics.html
*_execution_plan.html




内容也非常丰富，可以多收集一些看看，那么今天就到这里啦。

参考资料

All About the SQLT Diagnostic Tool (Doc ID 215187.1)
SQLT Usage Instructions (Doc ID 1614107.1)
SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)
How to Create a SQL-testcase Using the DBMS_SQLDIAG Package(Doc ID 727863.1)
Monitoring SQL statements with Real-Time SQL Monitoring (DocID 1380492.1)




全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！

————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————